Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


How To Create Stored Procedures and Stored Functions

There are advantages to using procedures and functions; however, the greatest advantage of using functions and procedures happens when the procedures and functions are stored in the database. Such procedures and functions are referred to as stored procedures and stored functions. A stored procedure or stored function has the advantage of being stored in the library cache in an already-parsed form, thus reducing parsing time.

To create a stored procedure or function, use the keywords CREATE PROCEDURE or CREATE FUNCTION with the same syntax as the PROCEDURE and FUNCTION commands shown earlier in this chapter. When creating a procedure or function, however, the IS keyword is replaced with the AS keyword. The following code is an example of how to create a stored procedure to retrieve some information from the DOGS table.


NOTE:  The typical stored procedure or function is called by an application program. In the following example, however, to make it easier to show a stored procedure, I chose to use SQL*Plus.
SQL> CREATE OR REPLACE PROCEDURE
  2      old_dogs
  3  AS
  4      CURSOR dog_cursor IS
  5      SELECT
  6          dogname, age, owner
  7      FROM dogs
  8      WHERE age > 8;
  9  BEGIN
 10      RDBMS_OUTPUT.PUT_LINE('Dogs older than 8 years old');
 11      RDBMS_OUTPUT.PUT_LINE('Name  Age  Owner');
 12      FOR dog IN dog_cursor LOOP
 13      RDBMS_OUTPUT.PUT_LINE(dog.dogname||'  '||dog.age||' '||dog.owner);
 14      END LOOP;
 15  END old_dogs;
 16  /

Procedure created.

To view the output of this stored procedure from SQL*Plus, you must to enable the SERVEROUTPUT option as follows:

SQL> set serveroutput on

The resulting output of this procedure is shown here:

SQL> execute old_dogs;
Dogs older than 8 years old
Name  Age  Owner
Shasta  9  Jones
Jessy  10  Wilson
Ruff    9  King

PL/SQL procedure successfully completed.

As you can see, to enable the stored procedure to return multiple rows selected from the DOGS table, it was necessary to declare a cursor. By looping through this cursor, you can output all the lines that were selected.

How To Replace Procedures and Functions

If the procedure or function is already stored in the library cache, you must replace, rather than create, the procedure or function. You do this by using the command CREATE OR REPLACE PROCEDURE or CREATE OR REPLACE FUNCTION. With this command, an already-present procedure or function is replaced; if it is not already present, it is created.

Packages

Packages are sets of related procedures or functions that are compiled and stored together in the data dictionary. Packages allow you to group together PL/SQL types, objects, and subprograms into a logical unit. When you link these logically related entities together, it can be easier to program and modify modules based on their function and relation. Performance is enhanced because the entire package is loaded into memory when it is first called, thus increasing the chance for a cache hit on a related function or object that is likely to be called soon.

Packages are actually created in a statement with two different parts. The first is the declaration part, where the package is defined. Then there is the package body definition, where the body of the package is defined. The syntax of the statement used to create the package definition is as follows:

CREATE PACKAGE package_name AS package_specification
     public type and object declaration
     subprogram definition
END [ package_name ];

The definition part of the package creation declares the parts of the package available to the user. The rest of the package definition is used by the user, but is not visible to the user. This second part has the following syntax:

CREATE PACKAGE BODY package_name AS package_body
     private type and object declaration
    subprogram bodies
[ BEGIN
          initialization statements ]
END [ package_name ];

The user application has knowledge of the package specification. The arrangement of the package-creation process has several advantages:


Advantage Comment

Portability The body of the package can change without requiring any changes to the application—as long as the package specification does not change.
Security The package can access tables you may not want the user to see. Because the package body is hidden from the user, some security can be maintained.
Modularity With packages, modules can have specific functions that can be logically grouped and specified.
Ease of design With packages, the specification part of the package can be completed first, thus allowing different teams to work on the package body and the application. Once the specification is completed, both groups can write to that specified interface.
Better performance Because the entire package is loaded into memory when the first component is accessed, additional calls to the package do not invoke disk I/O.

Summary

As you have seen, the use of functions, procedures, and packages can improve performance in several ways. These performance enhancements include reduction in the amount of data that must be transmitted across the network and an increase in hits in the data dictionary cache.

Because a procedure, function, or package is stored within the data dictionary, it is available for immediate use by your applications. Because stored procedures and functions are stored in the library cache in an already-parsed form, performance is improved. Any set of SQL statements that your application frequently uses can benefit from being made into a stored procedure or function.

There are very few SQL statements that cannot benefit from the use of procedures, functions, and packages. By storing these subprograms in the database, you reduce network traffic and increase performance in accessing these programs. Whenever possible, use stored procedures and packages; there is no disadvantage associated with their use.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.